<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Meter_tracking_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function getArea($area){
      $this->db->where('lookup_type',$area);
      return $this->db->get('app_lookup_values')->result();
    }

    public function getzoning($zone) {
      $this->db->where('zone_code', $zone);
      $query = $this->db->get('ar_zones');
      return $query->result();
  }

  public function getzoning2($zone) {
    $this->db->where('zone_code', $zone);
    $query = $this->db->get('ar_zone_values');
    return $query->result();
  }
    public function getData($data){
        $sql = "";
        $sql1 = ""; 
        $sql2 = "";
        $sql3 = "";
        $sql4 = "";
        $sql5 = "";
        $sql6 = "";
        $arr = array();
        if(!empty($data['account_number'])){
          $sql1 = " acct_num LIKE '". $data['account_number']."' ";
          array_push($arr,$sql1);
        }
        if(!empty($data['account_name'])){
          $sql2 = " acct_name LIKE '". $data['account_name']."' ";
          array_push($arr,$sql2);
        }
        if(!empty($data['meter_serial'])){
          $sql3 = " serial_number LIKE '". $data['meter_serial']."' ";
          array_push($arr,$sql3);
        }
        if(!empty($data['install_date'])){
          $sql4 = " install_date LIKE '". $data['install_date']."' ";
          array_push($arr,$sql4);
        }
        if(!empty($data['month_used'])){
          $sql5 = " TIMESTAMPDIFF(MONTH, install_date, NOW()) LIKE '". $data['month_used']."' ";
          array_push($arr,$sql5);
        }

        if(!empty($data['area']) && $data['area'] != 'all'){
          $sql6 = " aea.zone_1 = '". $data['area']."' ";
          array_push($arr,$sql6);
        }

        if(!empty($data) && $data['area'] != 'all'){
          $sql = "WHERE ".implode(' AND ',$arr);
        }

        $q = $this->db->query('SELECT IFNULL(serial_number,0) serial_number,
                                      acct_num account_number,
                                      ad.account_id account_id,
                                      aa.`acct_name` account_name,
                                      install_date,
                                      IFNULL(TIMESTAMPDIFF(MONTH, install_date, NOW()),0) AS month_used,
                                      aea.`zone_1` area,
                                      avl.`meaning`
                                    FROM ar_device ad 
                                    LEFT JOIN ar_account aa ON ad.`account_id` = aa.`account_id`
                                    LEFT JOIN ar_entity_address aea ON aea.`entity_id` = aa.`entity_id`
                                    LEFT JOIN app_lookup_values avl ON avl.lookup_code = aea.`zone_1` '.$sql);
        return $q->result();
    }

}